package pages;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import templates.Template;

import database.DBException;
import database.DBUtils;

/**
 * Servlet implementation class ReservationsList
 */
public class ReservationsList extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private static final String pageTitle = "Reservations List";

	private static final String templateName = "reservations_list.xml";
	private static final String rowTemplateName = "reservations_list_row.xml";
	private static final String tblTemplateName = "reservations_list_tbl";

	private static final XSLType xslType = XSLType.Reservations;

	private static final String instrumentsComboTemplateName = "instruments_combo";
	private static final String instrumentOptionTemplateName = "instrument_option";

	private static final int rowsOnPage = 10;

	private static final String defaultUserNameFilter = "";
	private static final int defaultInstrumentFilter = 0;
	private static final Format dateFormatter = new SimpleDateFormat("d/M/yyyy");

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public ReservationsList() {
		super();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		initFilterAttributes(request, false);
		int pageNow = 0;
		try {
			pageNow = Integer.parseInt(LMUtils
					.getRequestParameterValueNotEmpty(request, "p"));
		} catch (Exception e) {
		}
		try {
			Template template = new Template(templateName);
			template.addChange("reservation_list_rows", loadReservationRows(
					request, pageNow));

			String xml = template.applyChanges();
			// response.getWriter().print(xml);
			String xslName = LMUtils.getSessionParamenterValue(request,
					"xslName");
			String xsl = XSLT.applyXSL(xml, xslName, xslType);
			// response.getWriter().print(xsl);

			Template tblTemplate = new Template(tblTemplateName);
			// filter
			tblTemplate.addChange("error", LMUtils.getRequestAttribute(request,
					"error"));
			tblTemplate.addChange("userNameFilter", LMUtils
					.getSessionParamenterValue(request, "userNameFilter"));
			tblTemplate.addChange("instruments_combo",
					instrumentsCombo((Integer) request.getSession()
							.getAttribute("instrumentFilter")));
			tblTemplate.addChange("startDate",
					TimestampToString((Timestamp) request.getSession()
							.getAttribute("startTimestampFilter")));
			tblTemplate.addChange("endDate",
					TimestampToString((Timestamp) request.getSession()
							.getAttribute("endTimestampFilter")));

			// xsl select
			tblTemplate.addChange("xsl_select", XSLT.getXSLs(xslType, xslName)
					.applyChanges());

			// pages
			tblTemplate.addChange("pages", LMUtils.pagesText(
					"./ReservationsList", (Integer) request
							.getAttribute("totalPages"), pageNow));
			// menu
			// tblTemplate = LMUtils.adminMenu(tblTemplate);
			// table
			tblTemplate.addChange("reservations_list_tbl", (xsl != null) ? xsl
					: xml);
			response.getWriter().print(
					tblTemplate.createPage(pageTitle, LMUtils
							.getUserLogin(request)));

		} catch (Exception e) {
			ErrorPage.printErrorPage(e.getMessage(), request, response);
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		String error = "";
		try {
			LMUtils
					.setSessionParamenterValue(request, "userNameFilter",
							LMUtils.getRequestParameterValue(request,
									"userNameFilter"));
		} catch (MissingParameterException e) {
			// no userName Filter was asked
		}
		try {
			request.getSession().setAttribute(
					"instrumentFilter",
					Integer.parseInt(LMUtils.getRequestParameterValue(request,
							"instrumentFilter")));
		} catch (NumberFormatException e) {
			request.getSession().setAttribute("instrumentFilter",
					defaultInstrumentFilter);
		} catch (MissingParameterException e) {
			// no instrument Filter was asked
		}

		try {
			String startTimestampString = LMUtils.getRequestParameterValue(
					request, "Date1");
			request.getSession().setAttribute("startTimestampFilter",
					stringToTimestamp(startTimestampString, true));
		} catch (MissingParameterException e) {
			// no start time was asked
		} catch (ParseException e) {
			request.getSession().setAttribute("startTimestampFilter",
					getDefaultTimestamp(true));
		}
		try {
			String endTimestampString = LMUtils.getRequestParameterValue(
					request, "Date2");
			request.getSession().setAttribute("endTimestampFilter",
					stringToTimestamp(endTimestampString, false));
		} catch (MissingParameterException e) {
			// no end time was asked
		} catch (ParseException e) {
			request.getSession().setAttribute("endTimestampFilter",
					getDefaultTimestamp(false));
		}

		try {
			LMUtils.getRequestParameterValueNotEmpty(request, "clearFilter");
			initFilterAttributes(request, true);
		} catch (MissingParameterException e) {
			// no clear filter was asked
		}

		// XSL
		try {
			LMUtils.setSessionParamenterValue(request, "xslName", LMUtils
					.getRequestParameterValueNotEmpty(request, "xslName"));
		} catch (MissingParameterException e) {
			// no XSL change was asked
		}

		// cancel reservation
		try {
			LMUtils.getRequestParameterValueNotEmpty(request,
					"CancelReservetion");
			String userLogin = LMUtils.getRequestParameterValueNotEmpty(
					request, "userLogin");
			int instrumentID = Integer.parseInt(LMUtils
					.getRequestParameterValueNotEmpty(request, "instrumentID"));
			long startTimeSeconds = Integer
					.parseInt(LMUtils.getRequestParameterValueNotEmpty(request,
							"startTimestamp"));
			error = removeReservation(userLogin, instrumentID, startTimeSeconds);
		} catch (MissingParameterException e) {
			// no CancelReservetion was asked
		} catch (Exception e) {
			error = e.getMessage();
		}
		LMUtils.setRequestAttribute(request, "error", error);
		doGet(request, response);
	}

	/**
	 * Fetches all relevant reservations from DB and makes reservations-XML
	 * 
	 * @param request
	 * @param pageNow
	 * @return string with XML
	 * @throws InternalErrorException
	 * @throws DBException
	 */
	private String loadReservationRows(HttpServletRequest request, int pageNow)
			throws InternalErrorException, DBException {
		HttpSession session = request.getSession();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		PreparedStatement countStmt = null;
		ResultSet countRS = null;
		try {
			String userNameFilter = LMUtils.getSessionParamenterValue(request,
					"userNameFilter");
			int instrumentFilter = (Integer) session
					.getAttribute("instrumentFilter");
			Timestamp startTimestampFilter = (Timestamp) session
					.getAttribute("startTimestampFilter");
			Timestamp endTimestampFilter = (Timestamp) session
					.getAttribute("endTimestampFilter");

			String reservationsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.ReservationsTableName");
			String userTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.UsersTableName");
			String instrumentsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.InstrumentsTableName");
			String typesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.TypesTableName");
			String countQuery = "SELECT COUNT(*) ";
			String query = "SELECT A.`userLogin`, B.`name` as `userName`, D.`name` as `typeName`, A.`instrumentID`"
					+ ", DATE_FORMAT(A.`startTime`, '%e%, %b% %Y %H%:%i%:%s') as `startTime`"
					+ ", UNIX_TIMESTAMP(A.`startTime`) as `startTimestamp`"
					+ ", DATE_FORMAT(A.`endTime`, '%e%, %b% %Y %H%:%i%:%s') as `endTime` "
					+ ", C.Name as `instrumentName` "
					+ ", (A.`startTime` < NOW()) as `isPassed`";
			String whereQuery = " FROM `"
					+ reservationsTableName
					+ "` as A, `"
					+ userTableName
					+ "` as B, `"
					+ instrumentsTableName
					+ "` as C, `"
					+ typesTableName
					+ "` as D "
					+ " WHERE A.`userLogin` = B.`Login` AND A.`instrumentID` = C.`id` AND C.`typeID` = D.`id` "
					+ filterQuery(userNameFilter, instrumentFilter);
			countQuery += whereQuery + ";";
			query += whereQuery + " ORDER BY A.`startTime` LIMIT " + pageNow
					* rowsOnPage + ", " + rowsOnPage + ";";

			conn = DBUtils.getConnection();
			stmt = conn.prepareStatement(query);
			stmt = filterQuery(stmt, userNameFilter, instrumentFilter,
					startTimestampFilter, endTimestampFilter);
			rs = stmt.executeQuery();

			Template rowTemplate = new Template(rowTemplateName);
			String rowsText = "";
			while (rs.next()) {
				rowTemplate.addChange("userLogin", rs.getString("userLogin"));
				rowTemplate.addChange("instrumentID", rs
						.getString("instrumentID"));
				rowTemplate.addChange("userName", rs.getString("userName"));
				rowTemplate.addChange("typeName", rs.getString("typeName"));
				rowTemplate.addChange("startTimestamp", rs
						.getString("startTimestamp"));
				rowTemplate.addChange("startTime", rs.getString("startTime"));
				rowTemplate.addChange("endTime", rs.getString("endTime"));
				rowTemplate.addChange("instrumentName", rs
						.getString("instrumentName"));
				rowTemplate.addChange("can_cancel",
						rs.getBoolean("isPassed") ? "0" : "1");

				rowsText += rowTemplate.applyChanges();
			}

			// total pages
			countStmt = conn.prepareStatement(countQuery);
			countStmt = filterQuery(countStmt, userNameFilter,
					instrumentFilter, startTimestampFilter, endTimestampFilter);
			countRS = countStmt.executeQuery();
			countRS.next();
			request.setAttribute("totalPages", (int) Math.ceil(countRS
					.getFloat(1)
					/ rowsOnPage));

			return rowsText;
		} catch (SQLException e) {
			throw new DBException("Can't load Reservations List: "
					+ e.getMessage(), e);
		} finally {
			DBUtils.close(countRS);
			DBUtils.close(countStmt);
			DBUtils.close(rs);
			DBUtils.close(stmt);
			DBUtils.close(conn);
		}
	}

	/**
	 * Adds conditions to WHERE clause based on filter options
	 * 
	 * @param userNameFilter
	 * @param instrumentFilter
	 * @return String with conditions
	 */
	private String filterQuery(String userNameFilter, int instrumentFilter) {
		String filterQuery = "";
		if (!userNameFilter.equals(defaultUserNameFilter)) {
			filterQuery += " AND (LOWER(B.`Login`) LIKE ? OR LOWER(B.`Name`) LIKE ?)";
		}
		if (instrumentFilter != defaultInstrumentFilter) {
			filterQuery += " AND (C.`id` = ?)";
		}
		filterQuery += "AND A.`startTime` >= ? AND A.`startTime` <= ?";
		return filterQuery;
	}

	/**
	 * Adds conditions to WHERE clause based on filter options
	 * 
	 * @param stmt
	 * @param userNameFilter
	 * @param instrumentFilter
	 * @param startTimestampFilter
	 * @param endTimestampFilter
	 * @return PreparedStatement with applied values
	 * @throws SQLException
	 */
	private PreparedStatement filterQuery(PreparedStatement stmt,
			String userNameFilter, int instrumentFilter,
			Timestamp startTimestampFilter, Timestamp endTimestampFilter)
			throws SQLException {
		int index = 1;
		if (!userNameFilter.equals(defaultUserNameFilter)) {
			stmt.setString(index++, "%" + userNameFilter.toLowerCase() + "%");
			stmt.setString(index++, "%" + userNameFilter.toLowerCase() + "%");
		}
		if (instrumentFilter != defaultInstrumentFilter) {
			stmt.setInt(index++, instrumentFilter);
		}
		stmt.setTimestamp(index++, startTimestampFilter);
		stmt.setTimestamp(index++, endTimestampFilter);
		return stmt;
	}

	/**
	 * removes one reservation
	 * 
	 * @param userLogin
	 * @param instrumentID
	 * @param startTime
	 * @return error text
	 * @throws InternalErrorException
	 * @throws DBException
	 */
	private String removeReservation(String userLogin, int instrumentID,
			long startTime) throws InternalErrorException, DBException {
		if (isBeforeNow(startTime)) {
			return "The reservation time had already passed";
		}

		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String reservationsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.ReservationsTableName");
			String query = "DELETE FROM `"
					+ reservationsTableName
					+ "` WHERE `userLogin` = ? AND `instrumentID` = ? AND UNIX_TIMESTAMP(`startTime`) = ? LIMIT 1;";

			conn = DBUtils.getConnection();
			stmt = conn.prepareStatement(query);
			stmt.setString(1, userLogin);
			stmt.setInt(2, instrumentID);
			stmt.setLong(3, startTime);
			stmt.execute();
			return "";
		} catch (SQLException e) {
			throw new DBException(
					"Can't delete reservation: " + e.getMessage(), e);
		} finally {
			DBUtils.close(stmt);
			DBUtils.close(conn);
		}
	}

	/**
	 * Gets all the instruments and generates combo-box
	 * 
	 * @param selectedInstrument
	 * @return String with combo-box
	 * @throws InternalErrorException
	 * @throws DBException
	 */
	private String instrumentsCombo(int selectedInstrument)
			throws InternalErrorException, DBException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String instrumentsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.InstrumentsTableName");
			String typesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.TypesTableName");
			String query = "SELECT `id`, `typeName` FROM (SELECT 0 as `id` , 'No filter' as `typeName` UNION SELECT A.`id`, B.`name` as `typeName` FROM `"
					+ instrumentsTableName
					+ "` as A, `"
					+ typesTableName
					+ "` as B WHERE A.`typeID` = B.`id`) as C ORDER BY `id`;";

			conn = DBUtils.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			Template instrOptionTemplate = new Template(
					instrumentOptionTemplateName);
			String instText = "";
			while (rs.next()) {
				Integer instrID = rs.getInt("id");
				instrOptionTemplate.addChange("instrumentID", instrID
						.toString());
				instrOptionTemplate.addChange("typeName", rs
						.getString("typeName"));
				instrOptionTemplate.addChange("selected",
						instrID == selectedInstrument ? "selected=\"selected\""
								: "");
				instText += instrOptionTemplate.applyChanges();

			}

			Template instComboTemplate = new Template(
					instrumentsComboTemplateName);
			instComboTemplate.addChange("instruments_options", instText);
			return instComboTemplate.applyChanges();
		} catch (SQLException e) {
			throw new DBException("Can't load Instruments Combo: "
					+ e.getMessage(), e);
		} finally {
			DBUtils.close(rs);
			DBUtils.close(stmt);
			DBUtils.close(conn);
		}
	}

	/**
	 * Converts string to Timestamp
	 * 
	 * @param timestampString
	 * @param isStartTime
	 * @return
	 * @throws ParseException
	 */
	private Timestamp stringToTimestamp(String timestampString,
			boolean isStartTime) throws ParseException {
		SimpleDateFormat dfm = new SimpleDateFormat("d/M/yyyy HH:mm:ss");

		if (isStartTime)
			return new Timestamp(dfm.parse(timestampString + " 00:00:00")
					.getTime());
		else {
			return new Timestamp(dfm.parse(timestampString + " 23:59:59")
					.getTime());
		}

	}

	/**
	 * Takes current time and converts it to the timestamp of start or end ot
	 * the day
	 * 
	 * @param isStartTime
	 * @return Timestamp with start or end of "today"
	 */
	private Timestamp getDefaultTimestamp(boolean isStartTime) {
		SimpleDateFormat dfm = new SimpleDateFormat("d/M/yyyy HH:mm:ss");
		Calendar currDate = Calendar.getInstance();
		try {
			if (isStartTime) {
				return new Timestamp(dfm.parse(
						dateFormatter.format(currDate.getTime()) + " 00:00:00")
						.getTime());
			} else {
				return new Timestamp(dfm.parse(
						dateFormatter.format(currDate.getTime()) + " 23:59:59")
						.getTime());
			}
		} catch (ParseException e) {
			// "Can't parse time: " + e.getMessage();
			return null;
		}
	}

	private String TimestampToString(Timestamp timestamp) {
		return dateFormatter.format(timestamp);
	}

	/**
	 * Checks if the timestamp was in the past
	 * 
	 * @return true if timestamp was in the past. false otherwise
	 */
	private boolean isBeforeNow(long timestamp) {
		return timestamp < System.currentTimeMillis() / 1000;
	}

	/**
	 * Initiates some request variables
	 * 
	 * @param request
	 * @param initAnyway
	 */

	private void initFilterAttributes(HttpServletRequest request,
			boolean initAnyway) {
		HttpSession session = request.getSession();
		if (session.getAttribute("instrumentFilter") == null || initAnyway) {
			session.setAttribute("instrumentFilter", defaultInstrumentFilter);
		}
		if (session.getAttribute("startTimestampFilter") == null || initAnyway) {
			session.setAttribute("startTimestampFilter",
					getDefaultTimestamp(true));
		}
		if (session.getAttribute("endTimestampFilter") == null || initAnyway) {
			session.setAttribute("endTimestampFilter",
					getDefaultTimestamp(false));
		}
		if (initAnyway) {
			session.setAttribute("userNameFilter", defaultInstrumentFilter);
		}
	}
}
